﻿Imports MySql.Data
Imports System.Data


Public Class CnxMySql
    Private Shared Cmd As New MySqlClient.MySqlCommand
    Private Shared Cnx As New MySqlClient.MySqlConnection
    Private Shared DataAdapt As New MySqlClient.MySqlDataAdapter
    Public Shared MonDataSet As New Data.DataSet
    Private Shared cptvue As Integer
    Private Shared MaChaine As String
    Private Shared MaRequete As String

    Public Shared Sub AlimenterComboBox(ByRef macombo As ComboBox, ByVal marequete As String, ByVal mondisplay As String, ByVal mavaleur As String)

        macombo.DataSource = Executer_Requete_Select(marequete)
        macombo.DisplayMember = mondisplay
        If mavaleur <> "" Then
            macombo.ValueMember = mavaleur
        End If

    End Sub

    Public Shared Sub Executer_Requete_Action(ByVal marequeteaction As String)
        Try
            Cmd.CommandText = marequeteaction
            Cmd.ExecuteNonQuery()
            MessageBox.Show("requête action correctement éxécutée...!", "Action", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Erreur dans la tentative d'insertion.", MessageBoxButtons.OK, MessageBoxIcon.Error)


        End Try
    End Sub

    Public Shared Function Executer_Requete_Scalaire(ByVal marequetescalaire As String) As Integer
        Cmd.CommandText = marequetescalaire
        DataAdapt.SelectCommand = Cmd
        Return CInt(Cmd.ExecuteScalar)

    End Function

    Public Shared Function Executer_Requete_Select(ByVal marequeteselect As String) As Data.DataTable

        Dim result As New Data.DataTable
        Dim mavue As String = "mavue" & cptvue
        Try
            Cmd.CommandText = marequeteselect
            DataAdapt.SelectCommand = Cmd
            DataAdapt.Fill(MonDataSet, mavue)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        result = MonDataSet.Tables(mavue)
        cptvue = cptvue + 1
        Return result
    End Function

    Public Shared Function Generer_Cle_Primaire(ByVal nomcle As String, ByVal nomtable As String) As Integer
        Dim Cle As Integer
        MaChaine = "SELECT Max(" & nomcle & ") FROM " & nomtable
        Cmd.CommandText = MaChaine
        Cle = Cmd.ExecuteScalar()
        Return (Cle + 1)
    End Function

    Public Shared Function GetChamp_DataTable(ByVal lesdonnees As DataTable, ByVal ligne As Integer, ByVal colonne As Integer) As String
        Dim ret = lesdonnees.Rows(ligne).Item(colonne)
        Return ret

    End Function

    Public Shared Sub Initialiser_TextBoxs(ByRef unefeuille As Form)
        For Each ctrl As Control In unefeuille.Controls

            If TypeOf ctrl Is TextBox Then
                CType(ctrl, TextBox).Text = String.Empty
            End If
        Next ctrl

    End Sub

    Public Shared Sub Permission_Saisie(ByRef unefeuille As Form, ByRef liberer As Boolean)
        For Each ctrl As Control In unefeuille.Controls

            If TypeOf ctrl Is TextBox Then
                ctrl.Enabled = False
            End If
        Next ctrl
    End Sub

    Public Shared Sub Se_Connecter_MySql(ByVal login As String, ByVal passwd As String)
        MaChaine = ("Server=localhost;Database=gestfiches;Uid=" & login & ";Pwd=" & passwd)

        Cnx.ConnectionString = MaChaine
        Cnx.Open()
        MonDataSet = New Data.DataSet("gestfiches")
        MonDataSet.Clear()
        Cmd.CommandType = CommandType.Text
        Cmd.Connection = Cnx
    End Sub

    Public Sub Vider_Dataset()

    End Sub
    Public Function RemplaceApostrophesSlash(ByRef unechaine As String) As String
        Dim ch As String
        ch = unechaine.Replace("'", "/")
        Return ch
    End Function
    Public Function RemplaceSlashApostrophes(ByRef unechaine As String) As String
        Dim ch As String
        ch = unechaine.Replace("/", "'")
        Return ch
    End Function

    Public Function RemplaceSlashApostrophesDataTable(ByRef lesdonnees As DataTable)
        Dim nblignes As Integer
        Dim nbcolonnes As Integer
        Dim test As String = ""
        nblignes = lesdonnees.Rows.Count
        nbcolonnes = lesdonnees.Columns.Count
        Dim i, j As Integer
        For i = 0 To nblignes - 1
            For j = 0 To nbcolonnes - 1
                'Attention le replace ne s'applique qu'aux type String : utilisation de la classe debase Object :
                If Object.ReferenceEquals(lesdonnees.Rows(i).Item(j).GetType(), test.GetType()) Then
                    lesdonnees.Rows(i).Item(j) = RemplaceSlashApostrophes(lesdonnees.Rows(i).Item(j))
                End If
            Next
        Next
        Return lesdonnees
    End Function

End Class
